﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Collections;
using System.Data;

namespace User_Imaging
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        private String computerName = String.Empty;
        protected void Page_Load(object sender, EventArgs e)
        {
            System.Net.IPHostEntry host = System.Net.Dns.GetHostEntry(Context.Request.ServerVariables["REMOTE_HOST"]);
            computerName = host.HostName.Split('.')[0];
        }

        protected void gridSoftware_Remediation(object sender, GridViewCommandEventArgs e)
        {
        }
        protected void StepPackagedSoftwareValidation_DeActivate(object sender, EventArgs e)
        {
        }
        protected void imagingWizard_FinishButtonClick(object sender, WizardNavigationEventArgs e)
        {

        }
        protected void stepPackagedSoftwareValidation_Activate(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            DataRow dr;

            dt.Columns.Add(new DataColumn("Software Name", typeof(string)));
            dt.Columns.Add(new DataColumn("install", typeof(string)));
            dt.Columns.Add(new DataColumn("uninstall", typeof(string)));

            //Set Grid Caption
            gridSoftware.Caption = "<h2>Packaged Software Validation Table For " + computerName + "</h2>";

            SqlConnection connection = new SqlConnection("Data Source=MGOSCCCSP1.genmills.com;Initial Catalog=SMS_GMS;Integrated Security=SSPI");
            try
            {
                String username = User.Identity.Name.ToString().Split('\\')[1];
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "SELECT DISTINCT\n" +
                                      "pck.Manufacturer AS [Package Vendor],\n" +
                                      "       pck.Name AS [Package Name],\n" +
                                      "       pck.Version AS [Package Version],\n" +
                                      "           Case prg.programflags & power(2, 27)\n" +
                                      "           when '' then 'Restricted'\n" +
                                      "           else 'Any System'\n" +
                                      "           end as [OS Platform],\n" +
                                      "           Case when os1.PackageID is not null then '*' else '' end as [All x64 Windows 7],\n" +
                                      "           Case when os3.PackageID is not null then '*' else '' end as [All x86 Windows XP],\n" +
                                      "       prg.ProgramName AS [Program Name] \n" +
                                      "FROM     dbo.v_r_User AS U \n" +
                                      "         INNER JOIN dbo.v_ra_User_UserGroupName AS uug ON U.ResourceId = uug.ResourceId\n" +
                                      "         INNER JOIN dbo.v_CollectionRuleDirect AS crd ON uug.User_Group_Name0 = crd.RuleName\n" +
                                      "         INNER JOIN dbo.v_Advertisement AS Ad ON crd.CollectionId = Ad.CollectionId\n" +
                                      "         INNER JOIN v_Collection AS Col ON crd.CollectionId = Col.CollectionId\n" +
                                      "         INNER JOIN (SELECT Name,\n" +
                                      "                            Version,\n" +
                                      "                            Manufacturer,\n" +
                                      "                            PackageId,\n" +
                                      "                            Substring(DESCRIPTION,Charindex('PID=',DESCRIPTION) + 4,Len(DESCRIPTION)) AS Pid\n" +
                                      "                     FROM   v_Package) AS pck ON Ad.PackageId = pck.PackageId\n" +
                                      "         INNER JOIN (SELECT ProgramName,\n" +
                                      "                            PackageId,\n" +
                                      "                            programflags,\n" +
                                      "                            CASE\n" +
                                      "                              WHEN Charindex('Sequence=',COMMENT) > 0 THEN Substring(COMMENT,Charindex('Sequence=',COMMENT) + 9,Len(COMMENT))\n" +
                                      "                              ELSE '' END AS Sequence\n" +
                                      "                     FROM   v_Program) AS prg ON Ad.PackageId = prg.PackageId\n" +
                                      "           left join\n" +
                                      "                (select v_Package.PackageId, v_Program.programName, Platform, MinVersion, MaxVersion\n" +
                                      "                from v_Package\n" +
                                      "                LEFT OUTER JOIN v_Program ON v_Program.PackageId = v_Package.PackageId\n" +
                                      "                left join v_os_details on v_Package.PackageID=v_os_details.packageId and v_Program.programName=v_os_details.programName\n" +
                                      "                Where Platform = 'x64'\n" +
                                      "                and MinVersion = '6.10.0000.0'\n" +
                                      "                and MaxVersion = '6.10.9999.9999')\n" +
                                      "               as OS1 on OS1.PackageId = Pck.PackageId and OS1.programName = prg.programName\n" +
                                      "           left join\n" +
                                      "                (select v_Package.PackageId, v_Program.programName, Platform, MinVersion, MaxVersion\n" +
                                      "                from v_Package\n" +
                                      "                LEFT OUTER JOIN v_Program ON v_Program.PackageId = v_Package.PackageId\n" +
                                      "                left join v_os_details on v_Package.PackageID=v_os_details.packageId and v_Program.programName=v_os_details.programName\n" +
                                      "                Where Platform = 'I386'\n" +
                                      "                and MinVersion = '5.10.0000.0'\n" +
                                      "                and MaxVersion = '5.10.9999.9999')\n" +
                                      "               as OS3 on OS3.PackageId = Pck.PackageId and OS3.programName = prg.programName\n" +
                                      "WHERE    (Col.Name LIKE 'AD Group:%')\n" +
                                      "         AND (u.User_Name0 LIKE '521610')\n" +
                                      "ORDER BY [Package Name], [Program Name]";

                SqlDataReader queryReader = command.ExecuteReader();
                ArrayList softwareArray = new ArrayList();

       
                while (queryReader.Read())
                {
                    try
                    {
                        softwareArray.Add(new SoftwarePackage(queryReader["Package Name"].ToString(), queryReader["Package Version"].ToString(),
                                                              queryReader["OS Platform"].ToString(), queryReader["Program Name"].ToString(),
                                                              queryReader["All x64 Windows 7"].ToString(), queryReader["All x86 Windows XP"].ToString(),
                                                              queryReader["Package Vendor"].ToString()));
                    }
                    catch (Exception ex) { }
                }

                ArrayList usedNames = new ArrayList();
                foreach (SoftwarePackage package in softwareArray)
                {
                    if (!usedNames.Contains(package.ToString()))
                    {
                        dr = dt.NewRow();
                        dr[0] = package.DisplayName;

                        dt.Rows.Add(dr);
                    }
                }
                gridSoftware.DataSource = dt;
                gridSoftware.DataBind();               
            }
            finally
            {
                connection.Close();
                connection.Dispose();
            }
        }
        protected void stepIntroduction_Activate(object sender, EventArgs e)
        {

        }
        protected void stepSoftwareValidation_Activate(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            DataRow dr;

            dt.Columns.Add(new DataColumn("Installed Software", typeof(string)));
            dt.Columns.Add(new DataColumn("Entitled", typeof(string)));
            dt.Columns.Add(new DataColumn("Remediation", typeof(string)));
            
            //Set Grid Caption
            gridSoftware.Caption = "<h2>Software Validation Table For " + computerName + "</h2>";
            
            SqlConnection connection = new SqlConnection("Data Source=MGOSCCCSP1.genmills.com;Initial Catalog=SMS_GMS;Integrated Security=SSPI");
            try
            {
                ArrayList AddRemoveSoftware = new ArrayList();
                ArrayList ActiveDirectorySoftware = new ArrayList();
                DateTime computerDiscoveryDate = DateTime.Today.AddDays(1);

                String username = User.Identity.Name.ToString().Split('\\')[1];
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "select distinct DisplayName0, InstallDate0, Creation_Date0 " +
                                      "from v_add_remove_programs arp " +
                                      "inner join V_R_System vrs on arp.ResourceID = vrs.ResourceID " +
                                      "where name0 like '" + computerName + "' order by DisplayName0";

                SqlDataReader queryReader = command.ExecuteReader();

                while (queryReader.Read())
                {
                    try 
                    { 
                        AddRemoveSoftware.Add(new SoftwarePackage(queryReader.GetString(0),queryReader.GetString(1)));
                        if (computerDiscoveryDate == DateTime.Today.AddDays(1))
                        {
                            computerDiscoveryDate = queryReader.GetDateTime(2);
                        }
                    }
                    catch (Exception ex) { }
                }
                
                foreach (SoftwarePackage Package in AddRemoveSoftware)
                {
                    if (Package.InstallDate > computerDiscoveryDate)
                    {
                        dr = dt.NewRow();
                        dr[0] = Package.DisplayName;
                        dt.Rows.Add(dr);
                    }
                }
                gridSoftware.DataSource = dt;
                gridSoftware.DataBind();
            }
            finally
            {
                connection.Close();
                connection.Dispose();
            }
        }
        protected void stepScheduling_Activate(object sender, EventArgs e)
        {
        }
        protected void stepSummary_Activate(object sender, EventArgs e)
        {

        }
        
    }
}